﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DTO.Common;
using System.Data.Common;
using DAO.Common;
using System.Data;
using System.Transactions;
using ServiceInterfaces.Headquarters;
using System.ServiceModel;

namespace DAO.Stores
{
    public class ProductDAO : DAO.Common.DAO
    {
        public ProductDAO(string provider, string connsStr) : base(provider, connsStr) { }

        public bool DecreaseProductStock(int productCode, int amount)
        {

            using (TransactionScope ts = new TransactionScope(TransactionScopeOption.Required))
            {
                bool result = false;
                using (DbConnection conn = DBHelper.CreateDBConnection(Prodiver, ConnectionStr))
                {

                    DbCommand cmd = DBHelper.CreateCommandWithParameters(
                        conn,
                        "decreaseProductStock",
                        CommandType.StoredProcedure,
                        new List<ParameterHelper>{
                        new ParameterHelper{ DbType = DbType.Int32, Name = "productCode", Value = productCode },
                        new ParameterHelper{ DbType = DbType.Int32, Name = "amount", Value = amount }
                    });

                    conn.Open();
                    result = ((int)cmd.ExecuteScalar()) == 0 ? false : true;
                }


                ChannelFactory<IHeadquartersSales> headquarterSales = new ChannelFactory<IHeadquartersSales>("headquarterManagementMSMQ");
                IHeadquartersSales headquarter = headquarterSales.CreateChannel();
                headquarter.SellProduct(productCode, amount);
                headquarterSales.Close();

                ts.Complete();
                return result;
            }
        }

        public int GetProductStock(int productCode) 
        {
            int stock = 0;
            using (DbConnection conn = DBHelper.CreateDBConnection(Prodiver, ConnectionStr))
            {

                DbCommand cmd = DBHelper.CreateCommandWithParameters(
                    conn,
                    "productStock",
                    CommandType.StoredProcedure,
                    new List<ParameterHelper>{
                        new ParameterHelper{ DbType = DbType.Int32, Name = "productCode", Value = productCode }
                    });

                conn.Open();
                stock = (int)cmd.ExecuteScalar();
            }

            return stock;
        }

        public Product GetProduct(int productCode)
        {
            Product p = null;
            using (DbConnection conn = DBHelper.CreateDBConnection(Prodiver, ConnectionStr))
            {

                DbCommand cmd = DBHelper.CreateCommandWithParameters(
                    conn,
                    "getProduct",
                    CommandType.StoredProcedure,
                    new List<ParameterHelper>{
                        new ParameterHelper{ DbType = DbType.Int32, Name = "productCode", Value = productCode }
                    });

                conn.Open();
                using (DbDataReader reader = cmd.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        p = new Product();
                        p.FromDataReader(reader);
                    }
                }
            }

            return p;
        }

        public List<Product> GetProducts(int page, int recordsPerPage)
        {
            List<Product> list = new List<Product>();
            using (DbConnection conn = DBHelper.CreateDBConnection(Prodiver, ConnectionStr))
            {
                DbCommand cmd = DBHelper.CreateCommandWithParameters(
                    conn,
                    "getProducts",
                    CommandType.StoredProcedure,
                    new List<ParameterHelper> {
                        new ParameterHelper{ DbType = DbType.Int32, Name = "page", Value = page },
                        new ParameterHelper{ DbType = DbType.Int32, Name = "recordsPerPage", Value = recordsPerPage }
                    });

                conn.Open();
                using (DbDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Product p = new Product();
                        p.FromDataReader(reader);

                        list.Add(p);
                    }
                }
            }

            return list;
        }

        public int GetNumberOfProducts()
        {
            int total = 0;
            using (DbConnection conn = DBHelper.CreateDBConnection(Prodiver, ConnectionStr))
            {
                DbCommand cmd = DBHelper.CreateCommandWithParameters(
                    conn,
                    "getNumberOfProducts",
                    CommandType.StoredProcedure,
                    null);

                conn.Open();
                total = (int)cmd.ExecuteScalar();
            }

            return total;
        }

        public void AddProductStock(int productCode, int amount)
        {
            using (DbConnection conn = DBHelper.CreateDBConnection(Prodiver, ConnectionStr))
            {

                DbCommand cmd = DBHelper.CreateCommandWithParameters(
                    conn,
                    "addStock",
                    CommandType.StoredProcedure,
                    new List<ParameterHelper>{
                        new ParameterHelper{ DbType = DbType.Int32, Name = "productCode", Value = productCode },
                        new ParameterHelper{ DbType = DbType.Int32, Name = "amount", Value = amount }
                    });

                conn.Open();
                int rowsAffected = (int)cmd.ExecuteNonQuery();
                if (rowsAffected == 0)
                    throw new InvalidOperationException("The product does not exist on database");
            }
        }

        public void AddProduct(Product product)
        {
            using (DbConnection conn = DBHelper.CreateDBConnection(Prodiver, ConnectionStr))
            {

                DbCommand cmd = DBHelper.CreateCommandWithParameters(
                    conn,
                    "addProduct",
                    CommandType.StoredProcedure,
                    product.ToParameterList());

                conn.Open();
                cmd.ExecuteNonQuery();
            }
        }
    }
}
